MySQL集群搭建(1)-主备搭建

头像
程淇铭
    阅读 11 分钟
    4

    数据库在任何业务中都是最重要的环节之一，这就对数据库架构提出的较高的要求。单点数据库永远不应该出现在生产环境，我们已经目睹过太多由于单点、备份缺失造成的损失，所以，搭建高可用 MySQL 集群是非常有必要的。

    搭建集群有以下几点好处:

    1. 高可用性，在主节失效时自动切换，不需要技术人员紧急处理
    2. 高吞吐，可以多个节点同时提供读取数据服务，降低主节点负载，实现高吞吐
    3. 可扩展性强，支持在线扩容
    4. 无影响备份，在备节点进行备份操作不会对业务产生影响

    要说缺点，有以下几点:

    1. 架构复杂，在部署、管理方面对技术人员有要求
    2. 备节点拉取主节点日志时会对主节点服务器性能有一定影响
    3. 如果配置了半同步复制，会对事务提交有一点影响

    总的说，集群是一定要搭建的，谁敢把自己的数据跑在一个随时会有风险的数据库上呢。接下来我会以几篇文章介绍怎么从简单地主备模式到高可用架构。本节主要介绍如何搭建 MySQL 主备，注重操作，不会有太多理论讲解。

    1 环境准备

    1.1 启动数据库

    在两台机器分别启动 MySQL 实例, MySQL 搭建方式可以参考 MySQL 安装(二进制版)

    IP 系统 端口 MySQL版本 节点
    192.168.41.83 Centos6.8 3306 5.7.20 Master
    192.168.41.72 Centos6.8 3306 5.7.20 Salve

    关键配置:

    Master:

    [client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3306/mysql.sock

[mysqld]
datadir = /data/mysql_db/mysql_seg_3306
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 833306

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

    Salve

    [client]
port = 3306
default-character-set=utf8mb4
socket = /data/mysql_db/mysql_seg_3306/mysql.sock

[mysqld]
datadir = /data/mysql_db/mysql_seg_3306
basedir = /usr/local/mysql57
tmpdir = /tmp
socket = /data/mysql_db/mysql_seg_3306/mysql.sock
pid-file = /data/mysql_db/mysql_seg_3306/mysql.pid
skip-external-locking = 1
skip-name-resolve = 1
port = 3306
server_id = 723306
read_only=1

default-storage-engine = InnoDB
character-set-server = utf8mb4
default_password_lifetime=0

#### log ####
log_timestamps=system
log_bin = /data/mysql_log/mysql_seg_3306/mysql-bin
log_bin_index = /data/mysql_log/mysql_seg_3306/mysql-bin.index
binlog_format = row
relay_log_recovery=ON
relay_log=/data/mysql_log/mysql_seg_3306/mysql-relay-bin
relay_log_index=/data/mysql_log/mysql_seg_3306/mysql-relay-bin.index
log_error = /data/mysql_log/mysql_seg_3306/mysql-error.log

#### replication ####
replicate_wild_ignore_table = information_schema.%,performance_schema.%,sys.%

#### semi sync replication settings #####
plugin_dir=/usr/local/mysql57/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 5000

    配置解析

    • datadir, basedir, tmpdir 分别为数据文件位置、数据库程序安装位置、临时文件位置
    • server_id 实例id，注意，同一集群机器的 server_id 不能相同
    • read_only 是否只读, 一般在备库设置
    • log_bin, log_bin_index 二进制日志位置、二进制日志索引文件位置
    • binlog_format 二进制日志格式，row 表示记录每条数据变化情况、statement 表示记录相关 sql 语句、mixed 表示两种混用，在搭建集群的时候建议使用 row 格式，如果是用 sql 语句来同步数据很容易出现数据不一致的情况
    • relay_log_recovery slave 宕机后，假如中继日志损坏，则重新拉取日志，为了保证中继日志完整性，建议开启
    • relay_log, relay_log_index 中继日志以及中继日志索引文件位置
    • log_error 错误日志位置
    • replicate_wild_ignore_table 同步时需要忽略的表，这里我们忽略了系统统计表，如果出现奇怪的同步失败情况，可以尝试开启
    • plugin_dir 插件位置
    • plugin_load 启动时需要加载的插件
    • loose_rpl_semi_sync_master_enabled 是否开启无损半同步复制-主库（建议主备都开启,方便主备切换）
    • loose_rpl_semi_sync_slave_enabled 是否开启无损半同步复制-备库（建议主备都开启,方便主备切换）

    1.2 插入数据

    我们假设 Master 是正在使用的数据库，现在要在线搭建备库，我们往 Master 节点插入一些测试数据

    [mysql@mysql-test-83 ~]$ mydb-test_seg
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 29
Server version: 5.7.21-log MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

db83-3306>>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

db83-3306>>create database mytest;
Query OK, 1 row affected (0.00 sec)

db83-3306>>use mytest;
Database changed
db83-3306>>create table test1(
    -> id int not null primary key auto_increment,
    -> name varchar(16) not null default '',
    -> age int not null default 0
    -> ) engine = InnoDb charset = utf8;
Query OK, 0 rows affected (0.01 sec)
db83-3306>>insert into test1 values (0, 'a', 16), (0, 'b', 17), (0, 'c', 18), (0, 'd', 19);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

db83-3306>>select * from test1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  16 |
|  2 | b    |  17 |
|  3 | c    |  18 |
|  4 | d    |  19 |
+----+------+-----+
4 rows in set (0.00 sec)

    2 在线搭建主从

    现在我们的环境如下

    • 192.168.41.83:3306 Master 节点，正在使用
    • 192.168.41.72:3306 新搭建数据库，要在上面做 192.168.41.83 的备库

    2.1 创建同步用户

    我们创建一个用户名为 repl 的用户，授予 REPLICATION SLAVE 权限专门用来同步

    db83-3306>>CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';
Query OK, 0 rows affected (5.01 sec)

db83-3306>>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
Query OK, 0 rows affected (0.00 sec)

db83-3306>>flush privileges;
Query OK, 0 rows affected (0.00 sec)

    2.2 备份数据

    常用的备份数据的方式有 innobackupex 和 mysqldump，这里数据量少，我们用 mysqldump 进行全备

    [mysql@mysql-test-83 ~]$ /usr/local/mysql57/bin/mysqldump -S /data/mysql_db/mysql_seg_3306/mysql.sock -F --opt -R --single-transaction --master-data=2 --default-character-set=utf8 -A > mysql_backup_full.sql

    参数解析:

    • -S 选择 socket 文件，本机连接数据库可以用这种方法，也可以指定 ip、端口进行连接
    • -F 开始导出之前刷新日志

    • --opt 如果有这个参数表示同时激活了 mysqldump 命令的 quick，add-drop-table，add-locks，extended-insert，lock-tables 参数

      • --quick 代表忽略缓冲输出，mysqldump 命令直接将数据导出到指定的SQL文件
      • --add-drop-table 就是在每个 CREATE TABEL 命令之前增加 DROP-TABLE IF EXISTS 语句，防止数据表重名
      • --add-locks 在INSERT数据之前和之后锁定和解锁对应的数据表
      • --extended-insert 表示可以多行插入
    • -R 导出存储过程以及自定义函数, 如果有用到存储过程, 需要加这个参数
    • --single-transaction (innodb)设置事务的隔离级别为可重复读，即 REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据, 如果全部表都为 InnoDB 就带上这个参数，保证数据一致性，备份时不会锁表。如果有 MyISAM 的表，需要锁表备份才能保证数据的一致性
    • --lock-all-tables 备份过程加读锁, single-transaction 选项和 lock-all-tables 选项是二选一的
    • --master-data=2 记录当前二进制日志位置, master_data取1和取2的区别，只是后者把 change master ... 命令注释起来了
    • --default-character-set 选择编码, 这个选项非常重要， 编码选不对或者没有设置很容易造成乱码
    • -A 代表备份所有的库

    数据备份完毕后，把数据文件直接传输到 Slave 机器上

    [mysql@mysql-test-83 ~]$ ll
total 772
-rw-rw-r-- 1 mysql mysql 786921 Nov 17 10:38 mysql_backup_full.sql
[mysql@mysql-test-83 ~]$ rsync -avzP mysql_backup_full.sql 192.168.41.72:/home/mysql/

    2.3 数据恢复

    在 Slave 机器上直接执行 sql 文件导入数据

    [mysql@mysql-test-72 ~]$ ll mysql_backup_full.sql 
-rw-rw-r-- 1 mysql mysql 786921 Nov 17 10:38 mysql_backup_full.sql
[mysql@mysql-test-72 ~]$ /usr/local/mysql57/bin/mysql -S /data/mysql_db/mysql_seg_3306/mysql.sock < mysql_backup_full.sql

    导入完毕，我们可以看到数据和 Master 的备份数据一致

    db72-3306>>show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| mytest             |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

db72-3306>>use mytest;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
db72-3306>>show tables;
+------------------+
| Tables_in_mytest |
+------------------+
| test1            |
+------------------+
1 row in set (0.00 sec)

db72-3306>>select * from test1;
+----+------+-----+
| id | name | age |
+----+------+-----+
|  1 | a    |  16 |
|  2 | b    |  17 |
|  3 | c    |  18 |
|  4 | d    |  19 |
+----+------+-----+
4 rows in set (0.00 sec)

    2.4 开启同步

    回到备份文件，我们从头部找到 Master 备份时间点的二进制日志位置

    [mysql@mysql-test-72 ~]$ head -30 mysql_backup_full.sql | grep 'CHANGE MASTER TO MASTER_LOG_FILE'
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000004', MASTER_LOG_POS=154;

    MASTER_LOG_FILE 和 MASTER_LOG_POS 就是在 Master 执行 show master status 得到的二进制位置信息。现在，我们执行同步命令

    -- 重置复制
-- reset slave;

-- 同步配置
CHANGE MASTER TO
MASTER_HOST='192.168.41.83',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='repl',
MASTER_LOG_FILE='mysql-bin.000004',
MASTER_LOG_POS=154;

-- 开启同步
start slave

    实际执行结果如下

    db72-3306>>CHANGE MASTER TO
    -> MASTER_HOST='192.168.41.83',
    -> MASTER_PORT=3306,
    -> MASTER_USER='repl',
    -> MASTER_PASSWORD='repl',
    -> MASTER_LOG_FILE='mysql-bin.000004',
    -> MASTER_LOG_POS=154;
Query OK, 0 rows affected, 2 warnings (0.20 sec)

db72-3306>>start slave;
Query OK, 0 rows affected (0.01 sec)

    查看同步状态

    db72-3306>>show slave status \G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.41.83
                  Master_User: repl
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 154
               Relay_Log_File: mysql-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 154
              Relay_Log_Space: 527
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 833306
                  Master_UUID: 15958368-e9a0-11e8-a98c-ecb1d77febe4
             Master_Info_File: /data/mysql_db/mysql_seg_3306/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

ERROR: 
No query specified

    我们可以从 Slave_IO_Running, Slave_SQL_Running, Seconds_Behind_Master 这三个参数可以判断出同步状态是否正常

    • Slave_IO_Running 取 Master 日志的线程, Yes 为正在运行
    • Slave_SQL_Running 从日志恢复数据的线程, Yes 为正在运行
    • Seconds_Behind_Master 当前数据库相对于主库的数据延迟， 这个值是根据二进制日志的时间戳计算得到的(秒)

    从输出结果可以看到我们的同步是正常的，下面我们来测试一下

    2.5 同步测试

    在 Master 节点插入新数据

    db83-3306>>insert into test1 values(0, 'chengqm', 24);
Query OK, 1 row affected (0.00 sec)

db83-3306>>select * from test1;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | a       |  16 |
|  2 | b       |  17 |
|  3 | c       |  18 |
|  4 | d       |  19 |
|  5 | chengqm |  24 |
+----+---------+-----+
5 rows in set (0.00 sec)

    备节点检查数据同步状态

    db72-3306>>select * from test1;
+----+---------+-----+
| id | name    | age |
+----+---------+-----+
|  1 | a       |  16 |
|  2 | b       |  17 |
|  3 | c       |  18 |
|  4 | d       |  19 |
|  5 | chengqm |  24 |
+----+---------+-----+
5 rows in set (0.00 sec)

    可以看到数据已经同步到备节点，本次主备搭建完成

    linuxmysql
    程淇铭
    4.2k 声望1.7k 粉丝

    今晚请吃饭，随便坐

    « 上一篇
    MySQL 安装(二进制版)
    下一篇 »
    MySQL集群搭建(2)-主主从模式

    引用和评论

    被 2 篇内容引用
    推荐阅读
    头像
    『SEO实战密码』--每个Web开发者都应该了解一下SEO

    程淇铭赞 4阅读 2k

    头像
    在Java程序中监听mysql的binlog

    huan1993赞 9阅读 1.8k

    头像
    Mysql数据库sql优化案例

    杜若赞 4阅读 2k

    头像
    一份简单i3wm配置

    notusednow赞 2阅读 27.6k

    头像
    MySQL 亿级数据平滑迁移实战

    vivo互联网技术赞 2阅读 917

    头像
    记录一次Linux下安装Oracle数据库的过程

    少放香菜赞 1阅读 3.4k

    头像
    linux下使用笔记本的相关设置

    notusednow阅读 7.5k

    0 条评论
    得票最新